﻿using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Data;


namespace SQLServerHelper
{
    /// <summary>
    ///DBSqlHelper 的摘要说明

    /// </summary>
    [Serializable]
    public class SqlHelper
    {
        static SQLExceptionLog LogHelper = new SQLExceptionLog();
        private string _strConnectionString = null;
        /// <summary>
        /// 连接串

        /// </summary>
        public string ConnectionString
        {
            get
            {
                return this._strConnectionString;
            }
        }


        /// <summary>
        /// 默认初始化ConnectionString
        /// </summary>
        public SqlHelper()
        {
            _strConnectionString = GetConnection("ConnectionString");
        }

        public string GetConnection(string conName)
        {
            return System.Configuration.ConfigurationManager.ConnectionStrings[conName].ConnectionString;
        }
        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="strName">传入的connectionStrings节点中的name值</param>
        public SqlHelper(string strName)
        {
            _strConnectionString = GetConnection(strName);
        }


        /// <summary>
        /// 执行无参数sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>int</returns>
        public int SqlExecuteNoneQuery(string sql)
        {
            int result = -1;
            try
            {
                result = MsSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(sql, ex.Message, "sql");
            }
            return result;
        }
        /// <summary>
        /// 执行有参数的sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>int</returns>
        public int SqlExecuteNoneQuery(string sql, SqlParameter[] parameters)
        {
            int result = -1;
            try
            {
                result = MsSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql, parameters);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(sql, "sql", parameters, ex.Message);
            }
            return result;
        }
        /// <summary>
        /// 执行无参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>int</returns>
        public int ProcExecuteNoneQuery(string procName)
        {
            int result = -1;
            try
            {
                result = MsSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, procName);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(procName, ex.Message, "proc");
            }
            return result;
        }

        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">参数</param>
        /// <returns>int</returns>
        public int ProcExecuteNoneQuery(string procName, SqlParameter[] parameters)
        {
            int result = -1;
            try
            {
                result = MsSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, procName, parameters);
            }
            catch (Exception ex)
            {
                LogHelper.AddThrowErrorbLog(procName, "proc", parameters, ex.Message);
            }
            return result;
        }

        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">参传出参数,作用于return返回值</param>
        /// <returns>int</returns>
        public int ProcExecuteNoneQuery(string procName, SqlParameter[] parameters, out object result)
        {
            int count = -1;
            result = 0;
            try
            {
                count = MsSqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, procName, parameters);

                foreach (SqlParameter param in parameters)
                {
                    if (param.Direction == ParameterDirection.Output)
                    {
                        result = param.Value;
                        break;
                    }
                    else if (param.Direction == ParameterDirection.ReturnValue)
                    {
                        result = param.Value;
                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.AddThrowErrorbLog(procName, "proc", parameters, ex.Message);
            }
            return count;
        }



        /// <summary>
        /// 执行无参数sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>object</returns>
        public object SqlExecuteScalar(string sql)
        {
            object result = null;
            try
            {
                result = MsSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, sql);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(sql, ex.Message, "sql");
            }
            return result;
        }
        /// <summary>
        /// 执行有参数的sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>object</returns>
        public object SqlExecuteScalar(string sql, SqlParameter[] parameters)
        {
            object result = null;
            try
            {
                result = MsSqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, sql, parameters);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(sql, "sql", parameters, ex.Message);
            }
            return result;
        }
        /// <summary>
        /// 执行无参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>object</returns>
        public object ProcExecuteScalar(string procName)
        {
            object result = null;
            try
            {
                result = MsSqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, procName);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(procName, ex.Message, "proc");
            }
            return result;
        }
        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">参数</param>
        /// <returns>object</returns>
        public object ProcExecuteScalar(string procName, SqlParameter[] parameters)
        {
            object result = null;
            try
            {
                result = MsSqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, procName, parameters);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(procName, "proc", parameters, ex.Message);
            }
            return result;
        }
        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">参数</param>
        /// <param name="oresult">参数返回值</param>
        /// <returns></returns>
        public object ProcExecuteScalar(string procName, SqlParameter[] parameters, out object oresult)
        {
            object result = null;
            oresult = null;
            try
            {
                result = MsSqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, procName, parameters);
                foreach (SqlParameter param in parameters)
                {
                    if (param.Direction == ParameterDirection.Output)
                    {
                        oresult = param.Value;
                        break;
                    }
                    else if (param.Direction == ParameterDirection.ReturnValue)
                    {
                        oresult = param.Value;
                        break;
                    }
                }
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(procName, "proc", parameters, ex.Message);
            }
            return result;
        }
        /// <summary>
        /// 执行无参数sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>DataSet</returns>
        public DataSet SqlExecuteDataset(string sql)
        {
            DataSet result = new DataSet();
            try
            {
                result = MsSqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, sql);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(sql, ex.Message, "sql");
            }
            return result;
        }
        /// <summary>
        /// 执行有参数的sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>DataSet</returns>
        public DataSet SqlExecuteDataset(string sql, SqlParameter[] parameters)
        {
            DataSet result = new DataSet();
            try
            {
                result = MsSqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, sql, parameters);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(sql, "sql", parameters, ex.Message);
            }
            return result;
        }
        /// <summary>
        /// 执行无参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>DataSet</returns>
        public DataSet ProcExecuteDataset(string procName)
        {
            DataSet result = new DataSet();
            try
            {
                result = MsSqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, procName);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(procName, ex.Message, "proc");
            }
            return result;

        }
        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">参数</param>
        /// <returns>DataSet</returns>
        public DataSet ProcExecuteDataset(string procName, SqlParameter[] parameters)
        {
            DataSet result = new DataSet();
            try
            {
                result = MsSqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, procName, parameters);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(procName, "proc", parameters, ex.Message);
            }
            return result;
        }
        /// <summary>
        /// 执行无参数sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>DataTable</returns>
        public DataTable SqlExecuteDataTable(string sql)
        {
            DataTable result = new DataTable();
            try
            {
                result = MsSqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, sql).Tables[0];
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(sql, ex.Message, "sql");
            }
            return result;
        }
        /// <summary>
        /// 执行有参数的sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>DataTable</returns>
        public DataTable SqlExecuteDataTable(string sql, SqlParameter[] parameters)
        {
            DataTable result = new DataTable();
            try
            {
                result = MsSqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, sql, parameters).Tables[0];
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(sql, "sql", parameters, ex.Message);
            }
            return result;
        }
        /// <summary>
        /// 执行无参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>DataTable</returns>
        public DataTable ProcExecuteDataTable(string procName)
        {
            DataTable result = new DataTable();
            try
            {
                result = MsSqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, procName).Tables[0];
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(procName, ex.Message, "proc");
            }
            return result;
        }
        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">参数</param>
        /// <returns>DataTable</returns>
        public DataTable ProcExecuteDataTable(string procName, SqlParameter[] parameters)
        {
            DataTable result = new DataTable();
            try
            {
                result = MsSqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, procName, parameters).Tables[0];
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(procName, "proc", parameters, ex.Message);
            }
            return result;
        }
        /// <summary>
        /// 执行无参数sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader SqlExecuteReader(string sql)
        {
            SqlDataReader result = null;
            try
            {
                result = MsSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, sql);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(sql, ex.Message, "sql");
            }
            return result;
        }
        /// <summary>
        /// 执行有参数的sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader SqlExecuteReader(string sql, SqlParameter[] parameters)
        {
            SqlDataReader result = null;
            try
            {
                result = MsSqlHelper.ExecuteReader(ConnectionString, CommandType.Text, sql, parameters);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(sql, "sql", parameters, ex.Message);
            }
            return result;

        }
        /// <summary>
        /// 执行无参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader ProcExecuteReader(string procName)
        {
            SqlDataReader result = null;
            try
            {
                result = MsSqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, procName);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(procName, ex.Message, "proc");
            }
            return result;

        }
        /// <summary>
        /// 执行带参数的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="parameters">参数</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader ProcExecuteReader(string procName, SqlParameter[] parameters)
        {
            SqlDataReader result = null;
            try
            {
                result = MsSqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, procName, parameters);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(procName, "proc", parameters, ex.Message);
            }
            return result;

        }


        /// <summary>
        /// 执行带事务无参数的sql语句
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public int SqlExecuteNoneQuery(SqlTransaction transaction, string sql)
        {
            int result = -1;
            try
            {
                result = MsSqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sql);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(sql, ex.Message, "sql");
            }
            return result;
        }

        /// <summary>
        /// 执行带事务有参数的sql语句
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public int SqlExecuteNoneQuery(SqlTransaction transaction, string sql, SqlParameter[] parameters)
        {
            int result = -1;
            try
            {
                return MsSqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sql);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(sql, "sql", parameters, ex.Message);
            }
            return result;
        }

        /// <summary>
        /// 执行带事务有参数的存储过程
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public int ProcExecuteNoneQuery(SqlTransaction transaction, string procName)
        {
            int result = -1;
            try
            {
                return MsSqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, procName);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(procName, ex.Message, "proc");
            }
            return result;
        }


        /// <summary>
        /// 执行带事务有参数的存储过程
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public int ProcExecuteNoneQuery(SqlTransaction transaction, string procName, SqlParameter[] parameters)
        {
            int result = -1;
            try
            {
                return MsSqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, procName);
            }
            catch (Exception ex)
            {

                LogHelper.AddThrowErrorbLog(procName, "proc", parameters, ex.Message);
            }
            return result;
        }

    }
}